﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Common.Utilities.OpenXml;
using WindowsUserControl.Codes;
using WindowsUserControl.Dialogs;

namespace Workflows.Components.Reports
{
	/// <summary>
	/// 
	/// </summary>
	public partial class SetManipulationEditor : Form, IComponentEditor
	{
		#region fields
		private string leftTransformCode = string.Empty;
		private string rightTransformCode = string.Empty;
		private DataGridViewTextBoxCell cellTransformCode = null;
		#endregion

		/// <summary>
		/// 
		/// </summary>
		public SetManipulationEditor()
		{
			InitializeComponent();
		}

		private void SetManipulationEditor_Load(object sender, EventArgs e)
		{
			this.cbo_OpType.Items.Clear();
			for(int i=0;i<=3;i++)
			{
				SetManipulationMethod opType = (SetManipulationMethod) i;
				this.cbo_OpType.Items.Add(opType.ToString());
			}
		}

		#region Implementation of IComponentEditor

		private object _ActivityNode;
		private SetManipulationComponent _SetManipulator;

		public event UpdateComponentSettings UpdateComponentSettingEvent;

		/// <summary></summary>
		public object OwnerActivityNode
		{
			get { return _ActivityNode; }
			set { _ActivityNode=value; }
		}

		/// <summary></summary>
		public Type WorkflowComponentType
		{
			get { return typeof(SetManipulationComponent); }
		}

		/// <summary></summary>
		public void LoadSettings(GlobalVariable[] globalVars, WorkflowComponentBase[] fromComponents, object activityNode, WorkflowComponentBase wfComponent)
		{
			this._ActivityNode = activityNode;
			this._SetManipulator = (SetManipulationComponent) wfComponent;

			SetManipulationInstruction instruction = (SetManipulationInstruction) this._SetManipulator.Instruction;
			this.txt_ResultFilePath.Text = instruction.ResultFilePath;

			this.dv_Providers.Rows.Clear();
			this.dv_Fields.Rows.Clear();
			this.cbo_Set1.Items.Clear();
			this.cbo_Set2.Items.Clear();

			Dictionary<string,List<string>> setFields=new Dictionary<string, List<string>>();

			if(instruction.Providers !=null && instruction.Providers.Count>0)
			{
				foreach(string setName in instruction.Providers.Keys)
				{
					SetDataProvider provider = instruction.Providers[setName];
					DataGridViewRow drSet = new DataGridViewRow();
					DataGridViewTextBoxCell cellSetName = new DataGridViewTextBoxCell();
					cellSetName.Value = setName;
					drSet.Cells.Add(cellSetName);
					DataGridViewComboBoxCell cellSrcType = new DataGridViewComboBoxCell();
					cellSrcType.Items.Add(SetDataSourceType.DB.ToString());
					cellSrcType.Items.Add(SetDataSourceType.XML.ToString());
					cellSrcType.Items.Add(SetDataSourceType.ExcelFile.ToString());
					cellSrcType.Value = provider.SourceType.ToString();
					drSet.Cells.Add(cellSrcType);
					DataGridViewTextBoxCell cellDataPath = new DataGridViewTextBoxCell();
					cellDataPath.Value = provider.DataSourcePath;
					drSet.Cells.Add(cellDataPath);
					DataGridViewTextBoxCell cellRecordPath = new DataGridViewTextBoxCell();
					cellRecordPath.Value = provider.RecordListPath;
					drSet.Cells.Add(cellRecordPath);
					DataGridViewTextBoxCell cellKey = new DataGridViewTextBoxCell();
					cellKey.Value = provider.KeyFieldName;
					drSet.Cells.Add(cellKey);

					this.dv_Providers.Rows.Add(drSet);

					this.cbo_Set1.Items.Add(setName);
					this.cbo_Set2.Items.Add(setName);

					if(provider.FieldPaths !=null && provider.FieldPaths.Count>0)
					{
						List<string> fieldNames=new List<string>();
						foreach(string fieldLabel in provider.FieldPaths.Keys)
						{
							DataGridViewRow dvr = new DataGridViewRow();
							DataGridViewTextBoxCell cellSetName2 = new DataGridViewTextBoxCell();
							cellSetName2.Value = setName;
							dvr.Cells.Add(cellSetName2);
							DataGridViewTextBoxCell cellFieldLabel = new DataGridViewTextBoxCell();
							cellFieldLabel.Value = fieldLabel;
							dvr.Cells.Add(cellFieldLabel);
							DataGridViewTextBoxCell cellFieldPath = new DataGridViewTextBoxCell();
							cellFieldPath.Value = provider.FieldPaths[fieldLabel];
							dvr.Cells.Add(cellFieldPath);
							this.dv_Fields.Rows.Add(dvr);
							fieldNames.Add(fieldLabel);
						}
						if(!fieldNames.Contains(provider.KeyFieldName))
							fieldNames.Add(provider.KeyFieldName);
						setFields.Add(setName,fieldNames);
					}
				}
			}

			this.dv_Operations.Rows.Clear();
			if(instruction.Operations !=null && instruction.Operations.Count>0)
			{
                List<string> availableDataTables=new List<string>();
                if(instruction.Providers!=null && instruction.Providers.Count>0)
                {
                    foreach(string outputName in instruction.Providers.Keys)
                    {
                        availableDataTables.Add(outputName);
                    }
                }
			    int opAdded = 1;
                while(opAdded >0)
                {
                    opAdded = 0;
                    foreach (SetOperation op in instruction.Operations)
                    {
                        bool canAdd = true;
                        if (availableDataTables.Contains(op.OutputSetName))
                            canAdd = false;
                        else
                        {
                            foreach (string inputName in op.SetNames)
                            {
                                if (!string.IsNullOrEmpty(inputName) && !availableDataTables.Contains(inputName))
                                {
                                    canAdd = false;
                                    break;
                                }
                            }
                        }
                        if(canAdd)
                        {
                            opAdded++;
                            availableDataTables.Add(op.OutputSetName);
                        }
                        else
                        {
                            continue;
                        }
                        List<string> leftJoinFields = new List<string>();
                        List<string> rightJoinFields = new List<string>();
                        string leftCode = string.Empty;
                        string rightCode = string.Empty;
                        List<string> setNames2 = new List<string>();
                        if (op.OpType == SetManipulationMethod.Filter)
                        {
                            this.AddOperation(op, leftJoinFields, rightJoinFields, leftCode, rightCode);

                            setNames2.AddRange(setFields[op.SetNames[0]]);
                        }
                        else
                        {
                            foreach (SetJoin join in instruction.Joins)
                            {
                                if (join.LeftSetName == op.SetNames[0] && join.RightSetName == op.SetNames[1])
                                {
                                    leftJoinFields.AddRange(join.LeftKeyNames);
                                    rightJoinFields.AddRange(join.RightKeyNames);
                                    leftCode = join.LeftKeyTransformation;
                                    rightCode = join.RightKeyTransformation;
                                    break;
                                }
                            }
                            op.Order = this.dv_Operations.Rows.Count;
                            this.AddOperation(op, leftJoinFields, rightJoinFields, leftCode, rightCode);

                            setNames2.AddRange(setFields[op.SetNames[0]]);
                            foreach (string fieldInSet2 in setFields[op.SetNames[1]])
                            {
                                if (!setNames2.Contains(fieldInSet2))
                                    setNames2.Add(fieldInSet2);
                            }
                        }

                        setFields.Add(op.OutputSetName, setNames2);
                    }
                }
				
				this.dv_DerivedSets.Rows.Clear();
				foreach (SetOperation op in instruction.Operations)
				{
					List<string> derivedFields = setFields[op.OutputSetName];
					foreach (string derivedField in derivedFields)
					{
						DataGridViewRow drDerived = new DataGridViewRow();
						DataGridViewTextBoxCell cellSetName = new DataGridViewTextBoxCell();
						cellSetName.Value = op.OutputSetName;
						drDerived.Cells.Add(cellSetName);
						DataGridViewTextBoxCell cellField = new DataGridViewTextBoxCell();
						cellField.Value = derivedField;
						drDerived.Cells.Add(cellField);
						this.dv_DerivedSets.Rows.Add(drDerived);
					}
				}
			}

			this.dv_Export.Rows.Clear();
			foreach(string setName in setFields.Keys)
			{
				DataGridViewRow drExport = new DataGridViewRow();
				DataGridViewTextBoxCell cellSetNameExport = new DataGridViewTextBoxCell();
				cellSetNameExport.Value = setName;
				drExport.Cells.Add(cellSetNameExport);
				DataGridViewCheckBoxCell cellExport = new DataGridViewCheckBoxCell();
				if(instruction.ExportedDataSetNames !=null && instruction.ExportedDataSetNames.Contains(setName))
					cellExport.Value = true;
				else
					cellExport.Value = false;
				drExport.Cells.Add(cellExport);
				this.dv_Export.Rows.Add(drExport);
			}
		}

		/// <summary></summary>
		public void SaveSettings()
		{
			SetManipulationInstruction instruction = (SetManipulationInstruction) this._SetManipulator.Instruction;
			instruction.ResultFilePath = this.txt_ResultFilePath.Text;
			instruction.Providers=new Dictionary<string, SetDataProvider>();
			foreach(DataGridViewRow dr in this.dv_Providers.Rows)
			{
				if(!dr.IsNewRow)
				{
					string setName = (string) dr.Cells[0].Value;
					SetDataSourceType srcType = (SetDataSourceType) Enum.Parse(typeof (SetDataSourceType), (string) dr.Cells[1].Value);
					string dataPath = (string) dr.Cells[2].Value;
					string recordPath = (string) dr.Cells[3].Value;
					string keyName = (string) dr.Cells[4].Value;
					SetDataProvider provider=new SetDataProvider();
					provider.SetName = setName;
					provider.SourceType = srcType;
					provider.DataSourcePath = dataPath;
					provider.RecordListPath = recordPath;
					provider.KeyFieldName = keyName;
					provider.FieldPaths=new Dictionary<string, string>();
					foreach(DataGridViewRow dr2 in this.dv_Fields.Rows)
					{
						if(!dr2.IsNewRow && dr2.Cells[0].Value.ToString()==setName)
						{
							string label = (string) dr2.Cells[1].Value;
							string fieldPath = (string) dr2.Cells[2].Value;
							provider.FieldPaths.Add(label,fieldPath);
						}
					}
					instruction.Providers.Add(setName,provider);
				}
			}

			Dictionary<string,SetJoin> joins=new Dictionary<string, SetJoin>();
			instruction.Operations=new List<SetOperation>();
			instruction.Joins=new List<SetJoin>();
			foreach(DataGridViewRow dr in this.dv_Operations.Rows)
			{
				if (!dr.IsNewRow)
				{
					string setName1 = (string) dr.Cells[0].Value;
					string joinFields1 = (string) dr.Cells[1].Value;
					string code1 = (string) dr.Cells[2].Value;
					string setName2 = (string) dr.Cells[4].Value;
					string joinFields2 = (string) dr.Cells[5].Value;
					string code2 = (string) dr.Cells[6].Value;
					SetManipulationMethod opType =
						(SetManipulationMethod) Enum.Parse(typeof (SetManipulationMethod), (string) dr.Cells[8].Value);
					string outputSetName = (string) dr.Cells[9].Value;
					string filterClause = (string) dr.Cells[10].Value;
				    int order = int.Parse(dr.Cells[11].Value.ToString());
					if (!joins.ContainsKey(string.Format("{0}:{1}", setName1, setName2)))
					{
						SetJoin join = new SetJoin(setName1, StringUtil.Split(joinFields1), code1, setName2, StringUtil.Split(joinFields2),
						                           code2);
						joins.Add(string.Format("{0}:{1}", setName1, setName2), join);
					}
					if (opType == SetManipulationMethod.Filter)
					{
						SetOperation op1 =
							new SetOperation(order, new string[] {setName1},
							                 string.Format("{0}>{1}", setName1, filterClause),
							                 outputSetName);
						instruction.Operations.Add(op1);
					}
					else if (opType == SetManipulationMethod.Union)
					{
						SetOperation op2 =
                            new SetOperation(order, new string[] { setName1, setName2 },
							                 string.Format("{0}+{1}", setName1, setName2),
							                 outputSetName);
						instruction.Operations.Add(op2);
					}
					else if (opType == SetManipulationMethod.Intersection)
					{
						SetOperation op3 =
                            new SetOperation(order, new string[] { setName1, setName2 },
							                 string.Format("{0}/{1}", setName1, setName2),
							                 outputSetName);
						instruction.Operations.Add(op3);
					}
					else if (opType == SetManipulationMethod.Exclude)
					{
						SetOperation op4 =
                            new SetOperation(order, new string[] { setName1, setName2 },
							                 string.Format("{0}-{1}", setName1, setName2),
							                 outputSetName);
						instruction.Operations.Add(op4);
					}
				}
			}

			if(joins.Count>0)
			{
				instruction.Joins.AddRange(joins.Values);
			}

			instruction.ExportedDataSetNames=new List<string>();
			foreach(DataGridViewRow dr in this.dv_Export.Rows)
			{
				if(!dr.IsNewRow && dr.Cells[0].Value!=null && dr.Cells[1].Value!=null)
				{
					DataGridViewCheckBoxCell cellExport = (DataGridViewCheckBoxCell) dr.Cells[1];
					if((bool)cellExport.Value)
					{
						string setToExport = (string) dr.Cells[0].Value;
						instruction.ExportedDataSetNames.Add(setToExport);
					}
				}
			}

			this._SetManipulator.Instruction = instruction;
		}

		/// <summary></summary>
		public TestResult Test()
		{
			throw new System.NotImplementedException();
		}

		#endregion

		#region source type
		private void rd_Db_CheckedChanged(object sender, EventArgs e)
		{
			SetDataSourceType srcType = SetDataSourceType.DB;
			if(this.rd_Db.Checked)
				srcType = SetDataSourceType.DB;
			else if(this.rd_Xml.Checked)
				srcType = SetDataSourceType.XML;
			else if (this.rd_Excel.Checked)
				srcType = SetDataSourceType.ExcelFile;
			this.UpdateDataSourceType(srcType);
		}

		private void rd_Xml_CheckedChanged(object sender, EventArgs e)
		{
			SetDataSourceType srcType = SetDataSourceType.DB;
			if (this.rd_Db.Checked)
				srcType = SetDataSourceType.DB;
			else if (this.rd_Xml.Checked)
				srcType = SetDataSourceType.XML;
			else if (this.rd_Excel.Checked)
				srcType = SetDataSourceType.ExcelFile;
			this.UpdateDataSourceType(srcType);
		}

		private void rd_Excel_CheckedChanged(object sender, EventArgs e)
		{
			SetDataSourceType srcType = SetDataSourceType.DB;
			if (this.rd_Db.Checked)
				srcType = SetDataSourceType.DB;
			else if (this.rd_Xml.Checked)
				srcType = SetDataSourceType.XML;
			else if(this.rd_Excel.Checked)
				srcType = SetDataSourceType.ExcelFile;
			this.UpdateDataSourceType(srcType);
		}

		private void UpdateDataSourceType(SetDataSourceType srcType)
		{
			if(srcType==SetDataSourceType.DB)
			{
				this.lbl_DataSourcePath.Text = "Connection:";
				this.lbl_RecordPath.Text = "Table:";
				this.btn_BrowseDataSource.Visible = false;
			}
			else if(srcType==SetDataSourceType.XML || srcType==SetDataSourceType.ExcelFile)
			{
				this.lbl_DataSourcePath.Text = "Source file:";
				this.lbl_RecordPath.Text = "Record path:";
				this.btn_BrowseDataSource.Visible = true;
			}
		}
		#endregion

		#region path
		private void btn_BrowseDataSource_Click(object sender, EventArgs e)
		{
			OpenFileDialog dlg=new OpenFileDialog();
			if(this.rd_Xml.Checked)
			{
				dlg.Filter = "Xml files(*.find;*.xml)|*.find;*.xml|All files(*.*)|*.*";
			}
			else if(this.rd_Excel.Checked)
			{
				dlg.Filter = "Excel files(*.xls;*.xlsx)|*.xls;*.xlsx|All files(*.*)|*.*";
			}
			if(dlg.ShowDialog()==DialogResult.OK)
			{
				this.txt_DataSourcePath.Text = dlg.FileName;
			}
		}

		private void cbo_RecordPath_SelectedIndexChanged(object sender, EventArgs e)
		{
			if(this.cbo_RecordPath.SelectedIndex <0)
				return;
			if(this.rd_Db.Checked && !string.IsNullOrEmpty(this.txt_DataSourcePath.Text))
			{
				string tblName = this.cbo_RecordPath.SelectedItem.ToString();
				this.cbo_Key.Items.Clear();
				this.cbo_Fields.Items.Clear();
				TableColumn[] cols = DbUtil.GetTableColumns(DataProviderType.MSSQL, this.txt_DataSourcePath.Text, string.Empty,
				                                            tblName);
				if(cols !=null && cols.Length>0)
				{
					foreach(TableColumn col in cols)
					{
						this.cbo_Key.Items.Add(col.ColumnName);
						this.cbo_Fields.Items.Add(col.ColumnName);
					}
				}
			}
			else if(this.rd_Xml.Checked && !string.IsNullOrEmpty(this.txt_DataSourcePath.Text))
			{
				this.cbo_Key.Items.Clear();
				this.cbo_Fields.Items.Clear();
				XmlDocument xDoc=new XmlDocument();
				xDoc.Load(this.txt_DataSourcePath.Text);
				XmlNodeList xNodes = xDoc.SelectNodes(this.cbo_RecordPath.SelectedItem.ToString());
				if(xNodes !=null && xNodes.Count>0)
				{
					XmlNode xNode = xNodes[0];
					foreach(XmlAttribute attr in xNode.Attributes)
					{
						this.cbo_Key.Items.Add(attr.Name);
						this.cbo_Fields.Items.Add(attr.Name);
					}
				}
			}
			else if(this.rd_Excel.Checked && !string.IsNullOrEmpty(this.txt_DataSourcePath.Text))
			{
				this.cbo_Key.Items.Clear();
				this.cbo_Fields.Items.Clear();
				string sheetName = this.cbo_RecordPath.SelectedItem.ToString();
				// List<string> colHeaders = ExcelFileHelper.ReadColumnHeaders(this.txt_DataSourcePath.Text, sheetName);
			    List<string> colHeaders = ExcelReader.ReadColumnHeaders(this.txt_DataSourcePath.Text, sheetName);
                if (colHeaders != null && colHeaders.Count>0)
				{
					foreach(string colName in colHeaders)
					{
						this.cbo_Key.Items.Add(colName);
						this.cbo_Fields.Items.Add(colName);
					}
				}
			}
		}

		private void btn_RefreshRecordPaths_Click(object sender, EventArgs e)
		{
			if(this.rd_Db.Checked && !string.IsNullOrEmpty(this.txt_DataSourcePath.Text))
			{
				this.cbo_RecordPath.Items.Clear();
				string[] tblNames = DbUtil.GetTableNames(DataProviderType.MSSQL, string.Empty, this.txt_DataSourcePath.Text);
				if(tblNames !=null && tblNames.Length>0)
				{
					foreach(string tblName in tblNames)
					{
						this.cbo_RecordPath.Items.Add(tblName);
					}
				}
			}
			else if(this.rd_Xml.Checked && !string.IsNullOrEmpty(this.txt_DataSourcePath.Text))
			{
				XmlTextReader xReader=new XmlTextReader(File.OpenRead(this.txt_DataSourcePath.Text));
				this.cbo_RecordPath.Items.Clear();
				List<string> allPaths=new List<string>();
				int lastDepth = -1;
				string lastNodeName = string.Empty;
				Stack<string> elementNames=new Stack<string>();
				while(xReader.Read())
				{
					if(xReader.NodeType == XmlNodeType.Element)
					{
						int currentDepth = xReader.Depth;
						if(currentDepth > lastDepth)
						{
							lastDepth = currentDepth;
							lastNodeName = xReader.Name;
							elementNames.Push(lastNodeName);
							string xPath = this.GetXPath(elementNames);
							if(!allPaths.Contains(xPath))
								allPaths.Add(xPath);
						}
						else if(currentDepth==lastDepth)
						{
							if(xReader.Name==lastNodeName)
								continue;
							else
							{
								lastNodeName = xReader.Name;
								if (elementNames.Count > 0)
									elementNames.Pop();
								elementNames.Push(lastNodeName);
								string xPath = this.GetXPath(elementNames);
								if (!allPaths.Contains(xPath))
									allPaths.Add(xPath);
							}
						}
						else
						{
							lastDepth = xReader.Depth;
							lastNodeName = xReader.Name;
                            while (elementNames.Count > xReader.Depth)
                                elementNames.Pop();
                            elementNames.Push(lastNodeName);
						    string xPath = this.GetXPath(elementNames);
                            if(!allPaths.Contains(xPath))
                                allPaths.Add(xPath);
						}
					}
                    else if(xReader.NodeType==XmlNodeType.EndElement)
                    {
                        
                    }
				}
				xReader.Close();
				foreach(string xPath in allPaths)
				{
					this.cbo_RecordPath.Items.Add(xPath);
				}
			}
			else if(this.rd_Excel.Checked && !string.IsNullOrEmpty(this.txt_DataSourcePath.Text))
			{
				//List<string> sheetNames = ExcelFileHelper.GetWorksheetNames(this.txt_DataSourcePath.Text);
			    List<string> sheetNames = ExcelReader.GetWorksheetNames(this.txt_DataSourcePath.Text);
				this.cbo_RecordPath.Items.Clear();
				foreach(string sheetName in sheetNames)
				{
					this.cbo_RecordPath.Items.Add(sheetName);
				}
			}
		}

		private string GetXPath(Stack<string> elementNames)
		{
			string[] elementNamesCopy = elementNames.ToArray();
			string path = string.Empty;
			for (int i = elementNamesCopy.Length - 1; i >= 0; i--)
			{
				if (path.Length > 0)
					path += "/";
				path += elementNamesCopy[i];
			}
			return path;
		}
		#endregion

		#region set/fields

		private void cbo_Set1_SelectedIndexChanged(object sender, EventArgs e)
		{
			if(this.cbo_Set1.SelectedIndex>=0)
			{
				string setName = this.cbo_Set1.SelectedItem.ToString();
				List<string> keys=new List<string>();
				foreach(DataGridViewRow dr in this.dv_Providers.Rows)
				{
					if(!dr.IsNewRow && dr.Cells[0].Value!=null && dr.Cells[0].Value.ToString()==setName)
					{
						string key = (string) dr.Cells[4].Value;
						if(!keys.Contains(key))
							keys.Add(key);
					}
				}
			    bool foundSet = false;
				foreach(DataGridViewRow dr in this.dv_Fields.Rows)
				{
					if (!dr.IsNewRow && dr.Cells[0].Value != null && dr.Cells[0].Value.ToString() == setName)
					{
					    foundSet = true;
						string key = (string)dr.Cells[1].Value;
						if (!keys.Contains(key))
							keys.Add(key);
					}
				}
                if(!foundSet)
                {
                    foreach(DataGridViewRow dr in this.dv_DerivedSets.Rows)
                    {
                        if(!dr.IsNewRow && dr.Cells[0].Value!=null && dr.Cells[0].Value.ToString()==setName)
                        {
                            string key = (string) dr.Cells[1].Value;
                            if(!keys.Contains(key))
                                keys.Add(key);
                        }
                    }
                }
				this.clb_LeftJoinFields.Items.Clear();
				foreach(string key in keys)
				{
					this.clb_LeftJoinFields.Items.Add(key);
				}
			}
		}

		private void cbo_Set2_SelectedIndexChanged(object sender, EventArgs e)
		{
			if (this.cbo_Set2.SelectedIndex >= 0)
			{
				string setName = this.cbo_Set2.SelectedItem.ToString();
				List<string> keys = new List<string>();
				foreach (DataGridViewRow dr in this.dv_Providers.Rows)
				{
					if (!dr.IsNewRow && dr.Cells[0].Value != null && dr.Cells[0].Value.ToString() == setName)
					{
						string key = (string)dr.Cells[4].Value;
						if (!keys.Contains(key))
							keys.Add(key);
					}
				}
                bool foundSet = false;
				foreach (DataGridViewRow dr in this.dv_Fields.Rows)
				{
					if (!dr.IsNewRow && dr.Cells[0].Value != null && dr.Cells[0].Value.ToString() == setName)
					{
					    foundSet = true;
						string key = (string)dr.Cells[1].Value;
						if (!keys.Contains(key))
							keys.Add(key);
					}
				}
                if (!foundSet)
                {
                    foreach (DataGridViewRow dr in this.dv_DerivedSets.Rows)
                    {
                        if (!dr.IsNewRow && dr.Cells[0].Value != null && dr.Cells[0].Value.ToString() == setName)
                        {
                            string key = (string)dr.Cells[1].Value;
                            if (!keys.Contains(key))
                                keys.Add(key);
                        }
                    }
                }
				this.clb_RightJoinFields.Items.Clear();
				foreach (string key in keys)
				{
					this.clb_RightJoinFields.Items.Add(key);
				}
			}
		}

		private void btn_AddField_Click(object sender, EventArgs e)
		{
			if(this.cbo_Fields.SelectedIndex<0)
				return;

			string setName = this.txt_SetName.Text;
			// make sure set has already been added
			bool foundSetName = false;
			foreach(DataGridViewRow dr in this.dv_Providers.Rows)
			{
				if(!dr.IsNewRow && dr.Cells[0].Value !=null)
				{
					if(dr.Cells[0].Value.ToString()==setName)
					{
						foundSetName = true;
						break;
					}
				}
			}
			if(!foundSetName)
			{
				MessageBox.Show("Please add set first");
				return;
			}

			DataGridViewRow dvr = new DataGridViewRow();
			DataGridViewTextBoxCell cellSetName =new DataGridViewTextBoxCell();
			cellSetName.Value = setName;
			dvr.Cells.Add(cellSetName);
			DataGridViewTextBoxCell cellFieldLabel=new DataGridViewTextBoxCell();
			cellFieldLabel.Value = this.cbo_Fields.SelectedItem.ToString();
			dvr.Cells.Add(cellFieldLabel);
			DataGridViewTextBoxCell cellFieldPath = new DataGridViewTextBoxCell();
			cellFieldPath.Value = this.cbo_Fields.SelectedItem.ToString();
			dvr.Cells.Add(cellFieldPath);
			this.dv_Fields.Rows.Add(dvr);
		}

		private void btn_AddSet_Click(object sender, EventArgs e)
		{
			if(!string.IsNullOrEmpty(this.txt_SetName.Text))
			{
				string setName = this.txt_SetName.Text;
				foreach(DataGridViewRow dr in this.dv_Providers.Rows)
				{
					if(!dr.IsNewRow && dr.Cells[0].Value !=null)
					{
						if(dr.Cells[0].Value.ToString()==setName)
						{
							MessageBox.Show("The set is already added");
							return;
						}
					}
				}

				DataGridViewRow drSet=new DataGridViewRow();
				DataGridViewTextBoxCell cellSetName=new DataGridViewTextBoxCell();
				cellSetName.Value = setName;
				drSet.Cells.Add(cellSetName);
				SetDataSourceType srcType = SetDataSourceType.DB;
				if(this.rd_Xml.Checked)
					srcType = SetDataSourceType.XML;
				else if(this.rd_Excel.Checked)
					srcType = SetDataSourceType.ExcelFile;
				DataGridViewComboBoxCell cellSrcType=new DataGridViewComboBoxCell();
				cellSrcType.Items.Add(SetDataSourceType.DB.ToString());
				cellSrcType.Items.Add(SetDataSourceType.XML.ToString());
				cellSrcType.Items.Add(SetDataSourceType.ExcelFile.ToString());
				cellSrcType.Value = srcType.ToString();
				drSet.Cells.Add(cellSrcType);
				DataGridViewTextBoxCell cellDataPath=new DataGridViewTextBoxCell();
				cellDataPath.Value = this.txt_DataSourcePath.Text;
				drSet.Cells.Add(cellDataPath);
				DataGridViewTextBoxCell cellRecordPath=new DataGridViewTextBoxCell();
				cellRecordPath.Value = this.cbo_RecordPath.Text;
				drSet.Cells.Add(cellRecordPath);
				DataGridViewTextBoxCell cellKey=new DataGridViewTextBoxCell();
				cellKey.Value = this.cbo_Key.Text;
				drSet.Cells.Add(cellKey);

				this.dv_Providers.Rows.Add(drSet);

				this.cbo_Set1.Items.Add(setName);
				this.cbo_Set2.Items.Add(setName);

				DataGridViewRow drExport=new DataGridViewRow();
				DataGridViewTextBoxCell cellSetNameExport=new DataGridViewTextBoxCell();
				cellSetNameExport.Value = setName;
				drExport.Cells.Add(cellSetNameExport);
				DataGridViewCheckBoxCell cellExport=new DataGridViewCheckBoxCell();
				cellExport.Value = true;
				drExport.Cells.Add(cellExport);
				this.dv_Export.Rows.Add(drExport);
			}
		}

		private void dv_Providers_RowsRemoved(object sender, DataGridViewRowsRemovedEventArgs e)
		{
			if (this.dv_Providers.Rows.Count > e.RowIndex)
			{
				DataGridViewRow dr = this.dv_Providers.Rows[e.RowIndex];
				if (dr.Cells[0].Value != null)
				{
					string setRemoved = (string) dr.Cells[0].Value;
					for(int i=0;i<this.cbo_Set1.Items.Count;i++)
					{
						if(this.cbo_Set1.Items[i].ToString()==setRemoved)
						{
							this.cbo_Set1.Items.RemoveAt(i);
							break;
						}
					}
					for (int i = 0; i < this.cbo_Set2.Items.Count; i++)
					{
						if (this.cbo_Set2.Items[i].ToString() == setRemoved)
						{
							this.cbo_Set2.Items.RemoveAt(i);
							break;
						}
					}

					foreach(DataGridViewRow drField in this.dv_Fields.Rows)
					{
						if(!drField.IsNewRow && drField.Cells[0].Value!=null)
						{
							if(drField.Cells[0].Value.ToString()==setRemoved)
							{
								this.dv_Fields.Rows.Remove(drField);
							}
						}
					}

					foreach(DataGridViewRow drOp in this.dv_Operations.Rows)
					{
						if(!drOp.IsNewRow)
						{
							if(drOp.Cells[0].Value.ToString()==setRemoved)
								this.dv_Operations.Rows.Remove(drOp);
							else if(drOp.Cells[4].Value.ToString()==setRemoved)
								this.dv_Operations.Rows.Remove(drOp);
						}
					}
				}
			}
		}
		#endregion

		#region op
		private void cbo_OpType_SelectedIndexChanged(object sender, EventArgs e)
		{
			this.cbo_Set2.Enabled = true;
			this.clb_LeftJoinFields.Enabled = true;
			this.clb_RightJoinFields.Enabled = true;
			this.ck_RightTransform.Enabled = true;
			this.btn_RightTransform.Enabled = true;
			this.txt_Filter.Enabled = false;

			SetManipulationMethod opType = (SetManipulationMethod) this.cbo_OpType.SelectedIndex;
			if(opType==SetManipulationMethod.Filter)
			{
				this.clb_LeftJoinFields.Enabled = false;
				this.cbo_Set2.Enabled = false;
				this.clb_RightJoinFields.Enabled = false;
				this.ck_RightTransform.Enabled = false;
				this.btn_RightTransform.Enabled = false;
				this.txt_Filter.Enabled = true;
			}
		}

		private void ck_LeftTransform_CheckedChanged(object sender, EventArgs e)
		{
			this.leftTransformCode = string.Empty;
			this.btn_LeftTransformCode.Enabled = this.ck_LeftTransform.Checked;
		}

		private void ck_RightTransform_CheckedChanged(object sender, EventArgs e)
		{
			this.rightTransformCode = string.Empty;
			this.btn_RightTransform.Enabled = this.ck_RightTransform.Checked;
		}

		private void btn_AddOperation_Click(object sender, EventArgs e)
		{
			SetManipulationMethod opType = (SetManipulationMethod) this.cbo_OpType.SelectedIndex;
			List<string> leftJoinFields=new List<string>();
			if (this.clb_LeftJoinFields.Enabled)
			{
				for (int i = 0; i < this.clb_LeftJoinFields.Items.Count; i++)
				{
					if (this.clb_LeftJoinFields.GetItemChecked(i))
						leftJoinFields.Add(this.clb_LeftJoinFields.Items[i].ToString());
				}
			}
			List<string> rightJoinFields=new List<string>();
			if (this.clb_RightJoinFields.Enabled)
			{
				for(int i=0;i<this.clb_RightJoinFields.Items.Count;i++)
				{
					if(this.clb_RightJoinFields.GetItemChecked(i))
						rightJoinFields.Add(this.clb_RightJoinFields.Items[i].ToString());
				}
			}

		    int lastOpOrder = 0;
            foreach(DataGridViewRow dr in this.dv_Operations.Rows)
            {
                if(!dr.IsNewRow && dr.Cells[11].Value !=null)
                {
                    int opOrder = int.Parse(dr.Cells[11].Value.ToString());
                    if(opOrder>lastOpOrder)
                        lastOpOrder = opOrder;
                }
            }

			if (opType == SetManipulationMethod.Filter)
			{
				SetOperation filterOp=
					new SetOperation(lastOpOrder +1,
						new string[]{this.cbo_Set1.Text}, 
						string.Format("{0}>{1}",this.cbo_Set1.Text,this.txt_Filter.Text),
						this.txt_ResultSetName.Text);
				this.AddOperation(filterOp,leftJoinFields,rightJoinFields,this.leftTransformCode,this.rightTransformCode);
			}
			else if(opType==SetManipulationMethod.Union)
			{
				SetOperation unionOp =
                    new SetOperation(lastOpOrder + 1,
						new string[] {this.cbo_Set1.Text, this.cbo_Set2.Text},
						string.Format("{0}+{1}", this.cbo_Set1.Text, this.cbo_Set2.Text),
						this.txt_ResultSetName.Text);
				this.AddOperation(unionOp, leftJoinFields, rightJoinFields, this.leftTransformCode, this.rightTransformCode);
			}
			else if (opType == SetManipulationMethod.Exclude)
			{
				SetOperation excludeOp =
                    new SetOperation(lastOpOrder + 1,
						new string[] { this.cbo_Set1.Text, this.cbo_Set2.Text },
						string.Format("{0}-{1}", this.cbo_Set1.Text, this.cbo_Set2.Text),
						this.txt_ResultSetName.Text);
				this.AddOperation(excludeOp, leftJoinFields, rightJoinFields, this.leftTransformCode, this.rightTransformCode);
			}
			else if (opType == SetManipulationMethod.Intersection)
			{
				SetOperation intersetOp =
                    new SetOperation(lastOpOrder + 1,
						new string[] { this.cbo_Set1.Text, this.cbo_Set2.Text },
						string.Format("{0}/{1}", this.cbo_Set1.Text, this.cbo_Set2.Text),
						this.txt_ResultSetName.Text);
				this.AddOperation(intersetOp, leftJoinFields, rightJoinFields, this.leftTransformCode, this.rightTransformCode);
			}
		}

		private void AddOperation(SetOperation op, 
			List<string> leftJoinFields, 
			List<string> rightJoinFields, 
			string leftCode, 
			string rightCode)
		{
			DataGridViewRow dr = new DataGridViewRow();
			DataGridViewTextBoxCell cellSet1=new DataGridViewTextBoxCell();
			cellSet1.Value = op.SetNames[0];
			dr.Cells.Add(cellSet1);
			DataGridViewTextBoxCell cellLeftJoinFields=new DataGridViewTextBoxCell();
			cellLeftJoinFields.Value = StringUtil.ToString(leftJoinFields.ToArray());
			dr.Cells.Add(cellLeftJoinFields);
			DataGridViewTextBoxCell cellLeftTransformCode=new DataGridViewTextBoxCell();
			cellLeftTransformCode.Value = leftCode;
			dr.Cells.Add(cellLeftTransformCode);
			DataGridViewImageCell cellLeftTransformImg=new DataGridViewImageCell();
			if (string.IsNullOrEmpty(leftCode))
				cellLeftTransformImg.Value = this.imageList1.Images[0];
			else
				cellLeftTransformImg.Value = this.imageList1.Images[1];
			dr.Cells.Add(cellLeftTransformImg);
			DataGridViewTextBoxCell cellRightSet=new DataGridViewTextBoxCell();
			cellRightSet.Value = op.SetNames.Length == 2 ? op.SetNames[1] : string.Empty;
			dr.Cells.Add(cellRightSet);
			DataGridViewTextBoxCell cellRightJoinFields=new DataGridViewTextBoxCell();
			if (rightJoinFields != null && rightJoinFields.Count > 0)
				cellRightJoinFields.Value = StringUtil.ToString(rightJoinFields.ToArray());
			else
				cellRightJoinFields.Value = string.Empty;
			dr.Cells.Add(cellRightJoinFields);
			DataGridViewTextBoxCell cellRightTransformCode=new DataGridViewTextBoxCell();
			cellRightTransformCode.Value = rightCode;
			dr.Cells.Add(cellRightTransformCode);
			DataGridViewImageCell cellRightTransformImg=new DataGridViewImageCell();
			if (string.IsNullOrEmpty(rightCode))
				cellRightTransformImg.Value = this.imageList1.Images[0];
			else
				cellRightTransformImg.Value = this.imageList1.Images[1];
			dr.Cells.Add(cellRightTransformImg);
			DataGridViewComboBoxCell cellOpType=new DataGridViewComboBoxCell();
			for(int i=0;i<4;i++)
			{
				SetManipulationMethod opType = (SetManipulationMethod) i;
				cellOpType.Items.Add(opType.ToString());
			}
			cellOpType.Value = op.OpType.ToString();
			dr.Cells.Add(cellOpType);
			DataGridViewTextBoxCell cellOutputSetName=new DataGridViewTextBoxCell();
			cellOutputSetName.Value = op.OutputSetName;
			dr.Cells.Add(cellOutputSetName);
			DataGridViewTextBoxCell cellFilter=new DataGridViewTextBoxCell();
			cellFilter.Value = (string.IsNullOrEmpty(op.FilterClause) ? "" : op.FilterClause);
            dr.Cells.Add(cellFilter);
            DataGridViewTextBoxCell cellOrder=new DataGridViewTextBoxCell();
		    cellOrder.Value = op.Order;
		    dr.Cells.Add(cellOrder);
			this.dv_Operations.Rows.Add(dr);

			List<string> derivedFields=new List<string>();
			foreach(string setName in op.SetNames)
			{
				if(!string.IsNullOrEmpty(setName))
				{
					bool foundSrcSet = false;
					foreach(DataGridViewRow dr1 in this.dv_Providers.Rows)
					{
						if(!dr1.IsNewRow && dr1.Cells[0].Value.ToString()==setName)
						{
							string setKey = (string) dr1.Cells[4].Value;
							if(!derivedFields.Contains(setKey))
								derivedFields.Add(setKey);
						}
					}
					foreach(DataGridViewRow drField in this.dv_Fields.Rows)
					{
						if (!drField.IsNewRow && drField.Cells[0].Value.ToString() == setName)
						{
							foundSrcSet = true;
							string fieldName = (string) drField.Cells[1].Value;
							if(!derivedFields.Contains(fieldName))
								derivedFields.Add(fieldName);
						}
					}
					if(!foundSrcSet)
					{
						foreach(DataGridViewRow drField2 in this.dv_DerivedSets.Rows)
						{
							if(!drField2.IsNewRow && drField2.Cells[0].Value.ToString()==setName)
							{
								foundSrcSet = true;
								string fieldName = (string)drField2.Cells[1].Value;
								if (!derivedFields.Contains(fieldName))
									derivedFields.Add(fieldName);
							}
						}
					}
				}
			}
			foreach(string derivedField in derivedFields)
			{
				DataGridViewRow drDerived=new DataGridViewRow();
				DataGridViewTextBoxCell cellSetName=new DataGridViewTextBoxCell();
				cellSetName.Value = op.OutputSetName;
				drDerived.Cells.Add(cellSetName);
				DataGridViewTextBoxCell cellField=new DataGridViewTextBoxCell();
				cellField.Value = derivedField;
				drDerived.Cells.Add(cellField);
				this.dv_DerivedSets.Rows.Add(drDerived);
			}

			bool foundSet1 = false;
			for(int i=0;i<this.cbo_Set1.Items.Count;i++)
			{
				if(this.cbo_Set1.Items[i].ToString()==op.OutputSetName)
				{
					foundSet1 = true;
					break;
				}
			}
			if(!foundSet1)
			{
				this.cbo_Set1.Items.Add(op.OutputSetName);
			}
			bool foundSet2 = false;
			for (int i = 0; i < this.cbo_Set2.Items.Count; i++)
			{
				if (this.cbo_Set2.Items[i].ToString() == op.OutputSetName)
				{
					foundSet2 = true;
					break;
				}
			}
			if (!foundSet2)
			{
				this.cbo_Set2.Items.Add(op.OutputSetName);
			}
			bool foundExportSet = false;
			foreach(DataGridViewRow drExport in this.dv_Export.Rows)
			{
				if(!drExport.IsNewRow && drExport.Cells[0].Value!=null)
				{
					if(drExport.Cells[0].Value.ToString()==op.OutputSetName)
					{
						foundExportSet = true;
						break;
					}
				}
			}
			if(!foundExportSet)
			{
				DataGridViewRow drExport = new DataGridViewRow();
				DataGridViewTextBoxCell cellSetNameExport = new DataGridViewTextBoxCell();
				cellSetNameExport.Value = op.OutputSetName;
				drExport.Cells.Add(cellSetNameExport);
				DataGridViewCheckBoxCell cellExport = new DataGridViewCheckBoxCell();
				cellExport.Value = true;
				drExport.Cells.Add(cellExport);
				this.dv_Export.Rows.Add(drExport);
			}
		}


		private void btn_LeftTransformCode_Click(object sender, EventArgs e)
		{
			Dictionary<string,Type> joinFields=new Dictionary<string, Type>();
			for(int i=0;i<this.clb_LeftJoinFields.Items.Count;i++)
			{
				if(this.clb_LeftJoinFields.GetItemChecked(i))
					joinFields.Add(this.clb_LeftJoinFields.Items[i].ToString(),typeof(string));
			}
			FieldTransformCodeEditor codeEditor1=new FieldTransformCodeEditor(joinFields, this.leftTransformCode);
			codeEditor1.CodeUpdated += new FieldTransformCodeEditor.OnCodeUpdated(codeEditor1_CodeUpdated);
			codeEditor1.Show();
		}

		void codeEditor1_CodeUpdated(string code)
		{
			this.leftTransformCode = code;
		}

		private void btn_RightTransform_Click(object sender, EventArgs e)
		{
			Dictionary<string, Type> joinFields = new Dictionary<string, Type>();
			for (int i = 0; i < this.clb_RightJoinFields.Items.Count; i++)
			{
				if (this.clb_RightJoinFields.GetItemChecked(i))
					joinFields.Add(this.clb_RightJoinFields.Items[i].ToString(), typeof(string));
			}
			FieldTransformCodeEditor codeEditor2 = new FieldTransformCodeEditor(joinFields, this.rightTransformCode);
			codeEditor2.CodeUpdated += new FieldTransformCodeEditor.OnCodeUpdated(codeEditor2_CodeUpdated);
			codeEditor2.Show();
		}

		void codeEditor2_CodeUpdated(string code)
		{
			this.rightTransformCode = code;
		}

		private void dv_Operations_CellClick(object sender, DataGridViewCellEventArgs e)
		{
			if(e.RowIndex>=0 && e.ColumnIndex==3)
			{
				this.cellTransformCode = this.dv_Operations.Rows[e.RowIndex].Cells[2] as DataGridViewTextBoxCell;
				string code = this.cellTransformCode.Value == null ? string.Empty : (string) this.cellTransformCode.Value;
				string[] joinFields = StringUtil.Split((string)this.dv_Operations.Rows[e.RowIndex].Cells[1].Value);
				Dictionary<string,Type> args=new Dictionary<string, Type>();
				foreach(string joinField in joinFields)
				{
					args.Add(joinField,typeof(string));
				}
				FieldTransformCodeEditor codeEditor3=new FieldTransformCodeEditor(args,code);
				codeEditor3.CodeUpdated +=new FieldTransformCodeEditor.OnCodeUpdated(codeEditor3_CodeUpdated);
				codeEditor3.Show();
			}
			else if(e.RowIndex>=0 && e.ColumnIndex==7)
			{
				this.cellTransformCode = this.dv_Operations.Rows[e.RowIndex].Cells[6] as DataGridViewTextBoxCell;
				string code = this.cellTransformCode.Value == null ? string.Empty : (string)this.cellTransformCode.Value;
				string[] joinFields = StringUtil.Split((string)this.dv_Operations.Rows[e.RowIndex].Cells[5].Value);
				Dictionary<string, Type> args = new Dictionary<string, Type>();
				foreach (string joinField in joinFields)
				{
					args.Add(joinField, typeof(string));
				}
				FieldTransformCodeEditor codeEditor4 = new FieldTransformCodeEditor(args, code);
				codeEditor4.CodeUpdated += new FieldTransformCodeEditor.OnCodeUpdated(codeEditor4_CodeUpdated);
				codeEditor4.Show();
			}
		}

		void codeEditor4_CodeUpdated(string code)
		{
			if(this.cellTransformCode !=null)
				this.cellTransformCode.Value = code;
		}

		private void codeEditor3_CodeUpdated(string code)
		{
			if(this.cellTransformCode !=null)
				this.cellTransformCode.Value = code;
		}

		#endregion

		private void btn_BrowseResultFile_Click(object sender, EventArgs e)
		{
			SaveFileDialog dlg=new SaveFileDialog();
			dlg.Filter = "Xml files(*.xml)|*.xml|Excel files(*.xlsx)|*.xlsx|All files(*.*)|*.*";
			if(dlg.ShowDialog()==System.Windows.Forms.DialogResult.OK)
				this.txt_ResultFilePath.Text = dlg.FileName;
		}

		private void btn_OK_Click(object sender, EventArgs e)
		{
			this.SaveSettings();

			if (this.UpdateComponentSettingEvent != null)
				this.UpdateComponentSettingEvent(this._ActivityNode, this._SetManipulator);

			this.Close();
		}

		private void btn_Cancel_Click(object sender, EventArgs e)
		{
			this.Close();
		}

        private void btn_AddAllFields_Click(object sender, EventArgs e)
        {
            string setName = this.txt_SetName.Text;
            // make sure set has already been added
            bool foundSetName = false;
            foreach (DataGridViewRow dr in this.dv_Providers.Rows)
            {
                if (!dr.IsNewRow && dr.Cells[0].Value != null)
                {
                    if (dr.Cells[0].Value.ToString() == setName)
                    {
                        foundSetName = true;
                        break;
                    }
                }
            }
            if (!foundSetName)
            {
                MessageBox.Show("Please add set first");
                return;
            }

            for (int i = 0; i < this.cbo_Fields.Items.Count; i++)
            {
                string fieldName = this.cbo_Fields.Items[i].ToString();
                if (fieldName != this.cbo_Key.Text)
                {
                    DataGridViewRow dvr = new DataGridViewRow();
                    DataGridViewTextBoxCell cellSetName = new DataGridViewTextBoxCell();
                    cellSetName.Value = setName;
                    dvr.Cells.Add(cellSetName);
                    DataGridViewTextBoxCell cellFieldLabel = new DataGridViewTextBoxCell();
                    cellFieldLabel.Value = fieldName;
                    dvr.Cells.Add(cellFieldLabel);
                    DataGridViewTextBoxCell cellFieldPath = new DataGridViewTextBoxCell();
                    cellFieldPath.Value = fieldName;
                    dvr.Cells.Add(cellFieldPath);
                    this.dv_Fields.Rows.Add(dvr);
                }
            }
        }
	}
}
